package com.apes.fn.mdm.offer.repository;

import com.apes.fn.mdm.offer.model.DeptOfferGroup;
import com.apes.framework.jpa.repository.CustomRepository;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;

import java.util.List;
import java.util.Map;

public interface DeptOfferGroupRepository extends CustomRepository<DeptOfferGroup,String> {

    @Query(value = "SELECT A.dept_id FROM mas_dept_offer_group A WHERE A.name=:name", nativeQuery = true)
    List<String> findByName(@Param("name") String name);


    @Query(value = "SELECT * FROM mas_dept_offer_group A WHERE A.dept_id=:deptId", nativeQuery = true)
    DeptOfferGroup findByDeptId(@Param("deptId") String deptId);

    @Query(value = "SELECT * FROM mas_dept_offer_group A WHERE A.dept_id=:deptId AND A.name=:name", nativeQuery = true)
    DeptOfferGroup findByDeptAndName(@Param("deptId") String deptId,@Param("name") String name);

    @Query(value = "SELECT C.name,C.mobile_number FROM mas_offer_group A JOIN aut_users C ON A.personnel_name=C.ID" +
            " JOIN mas_dept_offer_group B ON A.NAME = B.NAME WHERE B.dept_id= :deptId",nativeQuery = true)
    Map queryContactPersonInfoByDeptId(@Param("deptId") String deptId);
}
